package org.gjy.m8.concurrent.cf;

import java.sql.*;
import java.util.concurrent.*;

/**
 * @author gjy
 * @version 1.0
 * @since 2025-10-09 15:01:10
 */
public class Cf4Test {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USERNAME = "your_username";
    private static final String PASSWORD = "your_password";

    /*public static void main(String[] args) {
        ExecutorService executor = Executors.newSingleThreadExecutor();

        try {
            String result = executeAdvancedSQLTaskWithTimeout(executor, 3, TimeUnit.SECONDS);
            System.out.println("SQL任务执行结果: " + result);
        } catch (TimeoutException e) {
            System.out.println("SQL任务超时被取消");
        } catch (Exception e) {
            System.out.println("SQL任务执行异常: " + e.getMessage());
        } finally {
            executor.shutdown();
        }
    }*/

    public static String executeAdvancedSQLTaskWithTimeout(ExecutorService executor,
                                                           long timeout, TimeUnit unit)
            throws TimeoutException, ExecutionException, InterruptedException {

        // 创建SQL任务，支持中断取消
        Callable<String> sqlTask = new Callable<String>() {
            private volatile Statement currentStatement;

            @Override
            public String call() throws Exception {
                Connection connection = null;
                ResultSet resultSet = null;

                try {
                    System.out.println("[" + Thread.currentThread().getName() + "] 正在建立数据库连接...");
                    connection = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);
                    System.out.println("[" + Thread.currentThread().getName() + "] 数据库连接建立成功");

                    // 创建Statement
                    currentStatement = connection.createStatement();

                    // 检查线程是否已被中断
                    if (Thread.currentThread().isInterrupted()) {
                        throw new InterruptedException("任务在开始前已被中断");
                    }

                    // 设置查询超时
                    currentStatement.setQueryTimeout((int) unit.toSeconds(timeout) + 1);

                    // 模拟长时间运行的SQL查询（使用MySQL的SLEEP函数）
                    String sql = "SELECT SLEEP(10) as result, 'completed' as status";

                    System.out.println("[" + Thread.currentThread().getName() + "] 开始执行SQL查询...");

                    // 执行查询
                    resultSet = currentStatement.executeQuery(sql);

                    // 检查线程中断状态
                    if (Thread.currentThread().isInterrupted()) {
                        throw new InterruptedException("SQL执行过程中被中断");
                    }

                    StringBuilder result = new StringBuilder();
                    while (resultSet.next()) {
                        result.append("查询结果: ")
                                .append(resultSet.getString("result"))
                                .append(", 状态: ")
                                .append(resultSet.getString("status"))
                                .append("\n");
                    }

                    System.out.println("[" + Thread.currentThread().getName() + "] SQL查询执行完成");
                    return result.toString();

                } catch (SQLException e) {
                    // 检查是否是由于中断导致的异常
                    if (Thread.currentThread().isInterrupted()) {
                        System.out.println("[" + Thread.currentThread().getName() + "] SQL执行被中断: " + e.getMessage());
                        throw new InterruptedException("SQL执行被中断");
                    }

                    // 检查是否是超时异常
                    if (e.getMessage().toLowerCase().contains("timeout") ||
                            e.getMessage().toLowerCase().contains("cancel")) {
                        System.out.println("[" + Thread.currentThread().getName() + "] SQL执行超时: " + e.getMessage());
                        throw new RuntimeException("SQL执行超时", e);
                    }

                    System.out.println("[" + Thread.currentThread().getName() + "] SQL执行异常: " + e.getMessage());
                    throw e;
                } finally {
                    // 关闭资源
                    closeResources(resultSet, currentStatement, connection);
                }
            }

            // 在任务外部提供取消SQL执行的方法
            public void cancelSQL() {
                if (currentStatement != null) {
                    try {
                        System.out.println("[" + Thread.currentThread().getName() + "] 正在取消SQL执行...");
                        currentStatement.cancel();
                        System.out.println("[" + Thread.currentThread().getName() + "] SQL执行已取消");
                    } catch (SQLException e) {
                        System.out.println("[" + Thread.currentThread().getName() + "] 取消SQL执行时出错: " + e.getMessage());
                    }
                }
            }
        };

        // 提交任务并获取Future
        Future<String> future = executor.submit(sqlTask);

        try {
            // 在指定时间内获取结果
            return future.get(timeout, unit);
        } catch (TimeoutException e) {
            System.out.println("[" + Thread.currentThread().getName() + "] 任务超时，正在取消SQL执行...");

            // 如果sqlTask实现了额外的取消方法，可以调用它
            if (sqlTask instanceof Runnable) {
                // 这里可以使用反射或其他方式调用cancelSQL方法
                // 但在实际应用中，更好的方式是创建一个自定义的Task类
            }

            // 取消任务（中断执行线程）
            boolean cancelled = future.cancel(true);
            System.out.println("[" + Thread.currentThread().getName() + "] 任务取消状态: " + cancelled);

            throw e;
        }
    }

    // 更好的实现 - 使用自定义Task类
    public static String executeCustomSQLTaskWithTimeout(ExecutorService executor,
                                                         long timeout, TimeUnit unit)
            throws TimeoutException, ExecutionException, InterruptedException {

        SQLTask sqlTask = new SQLTask();
        Future<String> future = executor.submit(sqlTask);

        try {
            return future.get(timeout, unit);
        } catch (TimeoutException e) {
            System.out.println("[" + Thread.currentThread().getName() + "] 任务超时，正在取消SQL执行...");

            // 调用自定义的SQL取消方法
            sqlTask.cancelSQL();

            // 取消任务
            boolean cancelled = future.cancel(true);
            System.out.println("[" + Thread.currentThread().getName() + "] 任务取消状态: " + cancelled);

            throw e;
        }
    }

    // 自定义SQL任务类
    static class SQLTask implements Callable<String> {
        private volatile Statement currentStatement;
        private volatile boolean isCancelled = false;

        @Override
        public String call() throws Exception {
            Connection connection = null;
            ResultSet resultSet = null;

            try {
                System.out.println("[" + Thread.currentThread().getName() + "] 正在建立数据库连接...");
                connection = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);
                System.out.println("[" + Thread.currentThread().getName() + "] 数据库连接建立成功");

                currentStatement = connection.createStatement();
                currentStatement.setQueryTimeout(15); // 设置15秒数据库超时

                // 检查是否已被取消
                if (isCancelled) {
                    throw new InterruptedException("任务已被取消");
                }

                // 执行长时间运行的查询
                String sql = "SELECT SLEEP(10) as result, 'completed' as status";
                System.out.println("[" + Thread.currentThread().getName() + "] 开始执行SQL查询...");

                resultSet = currentStatement.executeQuery(sql);

                // 检查中断
                if (Thread.currentThread().isInterrupted() || isCancelled) {
                    throw new InterruptedException("SQL执行过程中被中断");
                }

                StringBuilder result = new StringBuilder();
                while (resultSet.next()) {
                    result.append("查询结果: ")
                            .append(resultSet.getString("result"))
                            .append(", 状态: ")
                            .append(resultSet.getString("status"))
                            .append("\n");
                }

                System.out.println("[" + Thread.currentThread().getName() + "] SQL查询执行完成");
                return result.toString();

            } catch (SQLException e) {
                if (isCancelled || Thread.currentThread().isInterrupted()) {
                    System.out.println("[" + Thread.currentThread().getName() + "] SQL执行被取消或中断: " + e.getMessage());
                    throw new InterruptedException("SQL执行被取消");
                }

                if (e.getMessage().toLowerCase().contains("timeout") ||
                        e.getMessage().toLowerCase().contains("cancel") ||
                        e.getErrorCode() == 1317) { // MySQL查询中断错误码
                    System.out.println("[" + Thread.currentThread().getName() + "] SQL执行超时或被取消: " + e.getMessage());
                    throw new RuntimeException("SQL执行超时", e);
                }

                System.out.println("[" + Thread.currentThread().getName() + "] SQL执行异常: " + e.getMessage());
                throw e;
            } finally {
                closeResources(resultSet, currentStatement, connection);
            }
        }

        public void cancelSQL() {
            isCancelled = true;
            if (currentStatement != null) {
                try {
                    System.out.println("[" + Thread.currentThread().getName() + "] 正在取消SQL执行...");
                    currentStatement.cancel();
                    System.out.println("[" + Thread.currentThread().getName() + "] SQL执行已取消");
                } catch (SQLException e) {
                    System.out.println("[" + Thread.currentThread().getName() + "] 取消SQL执行时出错: " + e.getMessage());
                }
            }
        }
    }

    // 关闭数据库资源的工具方法
    private static void closeResources(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) resultSet.close();
        } catch (SQLException e) {
            System.out.println("关闭ResultSet时出错: " + e.getMessage());
        }

        try {
            if (statement != null) statement.close();
        } catch (SQLException e) {
            System.out.println("关闭Statement时出错: " + e.getMessage());
        }

        try {
            if (connection != null) connection.close();
        } catch (SQLException e) {
            System.out.println("关闭Connection时出错: " + e.getMessage());
        }
        System.out.println("[" + Thread.currentThread().getName() + "] 数据库资源已释放");
    }

    // 主方法测试
    public static void main(String[] args) {
        ExecutorService executor = Executors.newSingleThreadExecutor();

        try {
            // 测试自定义任务类
            String result = executeCustomSQLTaskWithTimeout(executor, 3, TimeUnit.SECONDS);
            System.out.println("SQL任务执行结果: " + result);
        } catch (TimeoutException e) {
            System.out.println("SQL任务超时被取消");
        } catch (Exception e) {
            System.out.println("SQL任务执行异常: " + e.getMessage());
            e.printStackTrace();
        } finally {
            executor.shutdown();
        }
    }

}
